package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import model.*;

public class DBEmployee {
	private Connection con;

	public DBEmployee(){
		con = DBConnection.getInstance().getDBcon();
	}

	public Employee findEmployee(int empID){
		String wClause = " empID = '" + empID + "'";
		return singleWhere(wClause);
	}

	public Employee searchEmployeeName(String attValue) {
		String wClause = "name like '%" + attValue + "%'";
		System.out.println("SearchEmployee " + wClause);
		return singleWhere(wClause);
	}

	public Employee searchEmployeeLName(String attValue) {
		String wClause = "lastName = '" + attValue + "'";
		System.out.println("SearchEmployee " + wClause);
		return singleWhere(wClause);
	}

	public int insertEmployee(Employee emp) throws Exception {

		int nextID = GetMax.getMaxID("Select max(ID) from employee");
		nextID = nextID + 1;
		System.out.println("next ID = " +  nextID);

		int rc = -1;
		String query="INSERT INTO employee(empName, empLastName, empID, empEmail)  VALUES('"+
  		emp.getEmpName()  + "','"  +
	    emp.getEmpLName()  + "','"  +
        nextID + "','" +
        emp.getEmpEmail() + "'";
                 
		System.out.println("insert : " + query);try{
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		rc = stmt.executeUpdate(query);
		stmt.close();
	}
	catch(SQLException ex) {
		System.out.println("Employee ikke oprettet");
		throw new Exception ("Employee is not inserted correct");
	}
	return(rc);
	}

	private Employee singleWhere(String wClause) {
		ResultSet results;
		Employee empObj = new Employee();
            
		String query =  buildQuery(wClause);
		System.out.println(query);
		try { // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
 		
			if( results.next() ){
				empObj = buildEmployee(results);
				stmt.close();

				//empObj = null;

			}
		}
		catch(Exception e) {
			System.out.println("Query exeption: " + e);
		}
		return empObj;
	}

	private Employee buildEmployee(ResultSet results) {
		Employee empObj = new Employee();
   
		try {
    		empObj.setEmpID(results.getInt("empID"));
    	    System.out.println("Emp id er = " + empObj.getEmpID());
    	    empObj.setEmpName(results.getString("empName"));
    	    empObj.setEmpLName(results.getString("empLastName"));
    	    empObj.setEmpEmail(results.getString("empEmail"));
		}
		catch(Exception e) {
			System.out.println("building employee object");
		}
    
	return empObj;
    
	}

	private String buildQuery(String wClause) {
		String query="SELECT empID, empName, empLastName, empEmail from Employee";	            
   
		if (wClause.length()>0) {
			query = query + " where " + wClause;
		}
		return query;
	}
}
                       


